﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration


Public Class DB_Class
    Inherits System.Web.UI.Page
    Private connection As SqlConnection
    Private ds As DataSet
    Private _queryString As String
    Private _connectionStriing As String = String.Empty
    Private tranSaction As SqlTransaction
    Public Property queryString() As String
        Get
            Return _queryString
        End Get
        Set(ByVal value As String)
            _queryString = value
        End Set
    End Property
    Public Property connectionString() As String
        Get
            Return _connectionStriing
        End Get
        Set(ByVal value As String)
            _connectionStriing = value
        End Set
    End Property
    Sub New()
        Dim connstr As String = ConfigurationManager.ConnectionStrings("IPORT").ConnectionString
        connection = New SqlConnection(connstr)
        ds = New DataSet
        tranSaction = Nothing
    End Sub
    Sub New(ByVal ConfigConStrName As String)
        Dim connstr As String = ConfigurationManager.ConnectionStrings(ConfigConStrName).ConnectionString
        connectionString = connstr
        connection = New SqlConnection(connstr)
        ds = New DataSet
        tranSaction = Nothing
    End Sub
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="tableName">Name of table</param>
    ''' <returns>Datable</returns>
    ''' <remarks></remarks>
    Public Function getTable(ByVal tableName As String) As DataTable
        connection.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = connection
        cmd.CommandText = _queryString
        da.SelectCommand = cmd

        If ds.Tables(tableName) IsNot Nothing Then
            ds.Tables(tableName).Clear()
            ds.Tables(tableName).Dispose()
        End If
        ' Try
        da.Fill(ds, tableName)
        'Catch ex As Exception
        '    Response.Write("Query Error: " & queryString)
        '    Page.Response.End()
        'End Try
        connection.Close()
        Return ds.Tables(tableName)
    End Function
    Public Function getSpecifiedTable(ByVal tableName As String) As DataTable
        connection.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim cmd As SqlCommand = New SqlCommand()
        _queryString = "SELECT * FROM " & tableName
        cmd.Connection = connection
        cmd.CommandText = _queryString
        da.SelectCommand = cmd

        If ds.Tables(tableName) IsNot Nothing Then
            ds.Tables(tableName).Clear()
        End If
        ' Try
        da.Fill(ds, tableName)
        'Catch ex As Exception
        '    Response.Write("Query Error: " & queryString)
        '    Page.Response.End()
        'End Try
        connection.Close()
        Return ds.Tables(tableName)
    End Function
    ''' <summary>
    ''' SELECT * FROM tableName WHERE where
    ''' </summary>
    ''' <param name="tableName">Name of table</param>
    ''' <param name="where">Where Clause</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function getSpecifiedTable(ByVal tableName As String, ByVal where As String) As DataTable
        connection.Open()
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = connection
        _queryString = "select * from " & tableName & " where " & where
        cmd.CommandText = _queryString
        da.SelectCommand = cmd

        If ds.Tables(tableName) IsNot Nothing Then
            ds.Tables(tableName).Clear()
        End If
        da.Fill(ds, tableName)
        connection.Close()
        Return ds.Tables(tableName)
    End Function
    Public Function insertUpdate() As String
        Dim ret As String = String.Empty
        connection.Open()
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = connection
        cmd.CommandText = _queryString
        Try
            tranSaction = connection.BeginTransaction()
            cmd.Transaction = tranSaction
            cmd.ExecuteNonQuery()
            tranSaction.Commit()
        Catch ex As Exception
            ret = ex.Message + "<br><br>Query String: " + _queryString
            tranSaction.Rollback()
        End Try
        connection.Close()
        Return ret
    End Function
    Public Function delete() As String
        Return insertUpdate()
    End Function
    Public Function getTableWhere(ByVal tableName As String, ByVal whereCondition As String) As DataRowCollection
        queryString = "select * from " + tableName + " where " + whereCondition
        Dim dt As DataTable = getTable(tableName)
        Return dt.Rows
    End Function
    Public Function GeneratID(ByVal tableName As String, ByVal primaryKey As String) As String
        Dim ret As String = "00001"
        queryString = "select TOP 1 " + primaryKey + " AS ID from " + tableName + " order by convert(int," + primaryKey + ") desc"
        Dim dt As DataTable = getTable("GenID")

        If dt.Rows.Count > 0 Then
            ret = CType((dt.Rows(0)("ID") + 1), Integer).ToString("#####00000")
        
        End If
        Return ret
    End Function
End Class
